﻿USE [TPAMainTZ]
GO

/****** Object:  StoredProcedure [dbo].[GetID]    Script Date: 01/14/2018 09:16:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[GetID]
(
	@sModName varchar(20),
	@rval varchar(50) output
)
as
--exec GetID
begin
Declare @sID varchar(14),
		@dToday datetime
Begin Tran
Begin Try
--锁定该条记录，好多人用lock去锁，起始这里只要执行一句update就可以了
--在同一个事物中，执行了update语句之后就会启动锁
	Update 编号表 set sID=sID where sModName=@sModName
	Select  @sID = sID From 编号表 where sModName=@sModName
	--因子表中没有记录，插入初始值
	If @sID is null
	Begin
		Select @sID = convert(bigint, convert(varchar(8), getdate(), 112) + '0001')
		Update 编号表 set sID=@sID where sModName=@sModName
	end else
	Begin 
		--因子表中没有记录
		Select @dToday = substring(@sID,1,8)
		--如果日期相等，则加1
		If @dToday = convert(varchar(8), getdate(), 112)
			Select @sID = convert(varchar(14), (convert(bigint, @sID) + 1))
		else 
			--如果日期不相等，则先赋值日期，流水号从1开始
			Select @sID = convert(bigint, convert(varchar(8), getdate(), 112) +'0001')
		Update 编号表 set sID =@sID where sModName=@sModName
	End
	set @rval =@sID 
	Commit Tran
End Try
Begin Catch
	Rollback Tran
	set @rval =ERROR_MESSAGE()
End Catch
end

GO

CREATE TABLE [dbo].[编号表](
	[sModName] [varchar](20) NOT NULL,
	[sID] [varchar](15) NULL
) ON [PRIMARY]

GO
